"""
用户搜索记录数据库操作类
提供用户搜索历史的批量插入和查询操作
"""

import aiomysql
from typing import List, Dict, Any
from db.operations import BaseOperations
from db import DatabaseConnection, db_connection


class UserSearchHistoryOperations(BaseOperations):
    """用户搜索记录数据库操作类"""

    def __init__(self):
        # 继承BaseOperations，指定表名为user_search_history
        super().__init__("user_search_history")
        self.db_connection: DatabaseConnection = db_connection

    async def batch_insert_search_history(
        self, search_history_data: List[Dict[str, Any]]
    ) -> int:
        """
        批量插入用户搜索记录

        Args:
            search_history_data (List[Dict[str, Any]]): 搜索记录数据列表
            每个字典应包含搜索记录的字段，例如：
            {
                "user_id": 1,
                "search_query": "python编程",
                "created_at": 1686825600,  # 创建时间戳（可选，自动生成）
                "updated_at": 1686825600   # 更新时间戳（可选，自动生成）
            }

        Returns:
            int: 成功插入的搜索记录数量
        """
        if not search_history_data:
            return 0

        # 验证必要字段
        valid_data = []
        for data in search_history_data:
            if "user_id" not in data or "search_query" not in data:
                print("警告：数据中缺少必要字段(user_id, search_query)，跳过该条记录")
                continue
            valid_data.append(data.copy())  # 创建副本避免修改原数据

        if not valid_data:
            return 0

        # 调用父类的批量插入方法，使用update策略（更新搜索时间）
        return await self.batch_insert(valid_data, on_duplicate="update")

    async def get_user_search_history_with_pagination(
        self,
        user_id: int,
        page: int = 1,
        limit: int = 10,
        order_by: str = "updated_at DESC",
    ) -> tuple[int, List[Dict[str, Any]]]:
        """
        分页获取用户搜索记录

        Args:
            user_id: 用户ID
            page: 页码，从1开始
            limit: 每页数量
            order_by: 排序方式，默认按更新时间降序排列

        Returns:
            tuple[int, List[Dict[str, Any]]]: 返回总数和搜索记录列表
        """
        offset = (page - 1) * limit

        # 查询分页数据的SQL
        data_sql = """
        SELECT *
        FROM user_search_history
        WHERE user_id = %s
        ORDER BY {order_by}
        LIMIT %s OFFSET %s
        """.format(
            order_by=order_by
        )

        # 查询总数的SQL
        count_sql = """
        SELECT COUNT(*) as total
        FROM user_search_history
        WHERE user_id = %s
        """

        pool = await self.db_connection.get_pool()

        try:
            async with pool.acquire() as conn:
                async with conn.cursor(aiomysql.DictCursor) as cursor:
                    # 先查询总数
                    await cursor.execute(count_sql, (user_id,))
                    count_result = await cursor.fetchone()
                    total = count_result["total"] if count_result else 0

                    # 查询分页数据
                    await cursor.execute(data_sql, (user_id, limit, offset))
                    search_history = await cursor.fetchall()

                    return total, search_history if search_history else []

        except Exception as e:
            print(f"查询用户搜索记录失败: {e}")
            return 0, []
